package cn.pconline.autoclub.task;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


/**
 * 统计车友会某天总体情况任务
 * @author zhoutianhuai
 *
 */
public class DailyGeneralSituationTask {
  private static Logger log = LoggerFactory.getLogger(DailyGeneralSituationTask.class);
  /**获得车友会某天用户访问总数sql**/
  private String visitorCountSql = "SELECT COUNT(1) num from ac_club_visitor v, ac_member_info b  WHERE v.user_id = b.user_id AND TO_DAYS(v.visitor_date) = TO_DAYS(?)";
  /**获得车友会某天发帖总数sql**/
  private String topicCountSql = "SELECT COUNT(1) num from ac_dyna_info_2015 a, ac_member_info b WHERE a.create_by = b.user_id AND TO_DAYS(a.create_date) = TO_DAYS(?) and a.type in (1,4)";
  /**获得车友会某天发表说说总数sql**/
  private String commentCountSql = "SELECT COUNT(1) num from ac_dyna_info_2015 a, ac_member_info b WHERE a.create_by = b.user_id AND  TO_DAYS(a.create_date) = TO_DAYS(?) and a.type = 2 ";
  /**获得车友会某天回复说说总数sql**/
  private String commentReplyCountSql = "SELECT COUNT(1) num FROM ac_dyna_reply a, ac_member_info b WHERE a.create_by = b.user_id AND TO_DAYS(a.create_date) = TO_DAYS(?)  and a.type = 2 ";
  /**获得车友会某天回复帖子总数sql**/
  private String topicReplyCountSql = "SELECT COUNT(1) num FROM ac_dyna_reply a, ac_member_info b WHERE a.create_by = b.user_id AND TO_DAYS(a.create_date) = TO_DAYS(?)  and a.type in (1, 4) ";
  /**获得车友会某天回复帖子总数sql**/
  private String praiseReplyCountSql = "SELECT COUNT(1) num FROM ac_dyna_praise a, ac_member_info b WHERE a.dynainfo_create_by = b.user_id AND TO_DAYS(a.praise_time) = TO_DAYS(?)";
  /**添加车友某天的概括记录**/
  private String addDailyGeneralSituationSql = "insert into ac_club_daily_general_situation "
                                               + " (belongs_date, visitor_count, topic_count, comment_count, comment_reply_count, topic_reply_count, praise_count, create_at) values (?,?,?,?,?,?,?,now())"
                                               + " ON DUPLICATE KEY UPDATE  visitor_count = ?, topic_count = ?, comment_count = ?, comment_reply_count = ?, topic_reply_count = ?, praise_count = ?";
 
  public static void setLog(Logger log) {
	  DailyGeneralSituationTask.log = log;
  }
  
  public static void main(String[] args) {
	  //初始化Log
      AppUtils.initLog(DailyGeneralSituationTask.class);
      Date d = null;
      if (args != null && args.length == 1) {
    	  d = DateUtils.parseDate(args[0]);
      }else{
    	  d = DateUtils.getLastDay(new Date());
      }
      DailyGeneralSituationTask t = new DailyGeneralSituationTask();
      AppUtils.log.info("run countDailyGeneralSituation start...");
      try {
         t.countDailyGeneralSituation(d);
      } catch (SQLException ex) {
          AppUtils.log.error("countDailyGeneralSituation fail!", ex);
      }
      AppUtils.log.info("countDailyGeneralSituation done!");
  }
  
  /**
   * 统计昨天平台登录用户总数、车友会用户发表主题帖总数、车友会用户发表说说总数、说说评论总数
   *
   * @param petrolMap
   * @throws SQLException
   */
  private void countDailyGeneralSituation(Date cur) throws SQLException {
      Connection conn = DBUtils.getConnection();
      boolean oldCommit = DBUtils.getAutoCommitValue(conn);
      conn.setAutoCommit(false);
      PreparedStatement ps = null;
      ResultSet rs = null;
      String dateStr  = DateUtils.format(cur, "yyyy-MM-dd");
      try {
          log.info("---->统计{}车友会平台登录用户总数,车友会用户发表主题帖总数,车友会用户发表说说总数,说说评论总数---->准备执行……", dateStr);
          long start = System.currentTimeMillis();
          /**
           * 统计昨天登录用户总数
           */
          int visitorNum = 0;
          ps = conn.prepareStatement(visitorCountSql);
          ps.setString(1, dateStr);
          rs = ps.executeQuery();
          while (rs.next()) {
        	  visitorNum = (rs.getInt("num"));
          }

          /**
           * 统计昨天车友会每天发帖总数
           */
          int topicNum = 0;
          ps = conn.prepareStatement(topicCountSql);
          ps.setString(1, dateStr);
          rs = ps.executeQuery();
          while (rs.next()) {
        	  topicNum = (rs.getInt("num"));
          }

          /**
           * 统计昨天车友会发说说总数
           */
          int commentNum = 0;
          ps = conn.prepareStatement(commentCountSql);
          ps.setString(1, dateStr);
          rs = ps.executeQuery();
          if (rs.next()) {
        	  commentNum = (rs.getInt("num"));
          }
          
          /**
           * 统计昨天车友会说说回复总数
           */
          int commentReplyNum = 0;
          for(int i = 0; i < 10; i++){//统计九个分表总数
        	 String Sql = commentReplyCountSql.replace("ac_dyna_reply", "ac_dyna_reply_"+i);
             ps = conn.prepareStatement(Sql);
             ps.setString(1, dateStr);
             rs = ps.executeQuery();
             if (rs.next()) {
            	 commentReplyNum += (rs.getInt("num"));
             }
          }
          
          /**
           * 统计昨天车友会帖子回复总数
           */
          int topicReplyNum = 0;
          for(int i = 0; i < 10; i++){//统计九个分表总数
        	 String Sql = topicReplyCountSql.replace("ac_dyna_reply", "ac_dyna_reply_"+i);
             ps = conn.prepareStatement(Sql);
             ps.setString(1, dateStr);
             rs = ps.executeQuery();
             if (rs.next()) {
            	 topicReplyNum += (rs.getInt("num"));
             }
          }
          
          /**
           * 统计昨天车友会点赞总数
           */
          int praiseNum = 0;
          for(int i = 0; i < 10; i++){//统计九个分表总数
             String Sql = praiseReplyCountSql.replace("ac_dyna_praise", "ac_dyna_praise_"+i);
             ps = conn.prepareStatement(Sql);
             ps.setString(1, dateStr);
             rs = ps.executeQuery();
             if (rs.next()) {
            	 praiseNum += (rs.getInt("num"));
             }
          }

          //添加车友会概况记录
          ps = conn.prepareStatement(addDailyGeneralSituationSql);
          ps.setInt(1, DateUtils.getTodayYmd(cur));   
          ps.setInt(2, visitorNum);
          ps.setInt(3, topicNum);
          ps.setInt(4, commentNum);
          ps.setInt(5, commentReplyNum);
          ps.setInt(6, topicReplyNum);
          ps.setInt(7, praiseNum);
          ps.setInt(8, visitorNum);
          ps.setInt(9, topicNum);
          ps.setInt(10, commentNum);
          ps.setInt(11, commentReplyNum);
          ps.setInt(12, topicReplyNum);
          ps.setInt(13, praiseNum);
          int i = ps.executeUpdate();

          long end = System.currentTimeMillis();
          log.info("耗时{}秒", (end - start) / 1000.0);
          log.info("---->统计{}车友会平台登录用户总数,车友会用户发表主题帖总数,车友会用户发表说说总数,说说评论总数---->执行完毕！", dateStr);
      } catch (Exception e) {
          log.error("countDailyGeneralSituation exception!", e);
      } finally {
          conn.commit();
          conn.setAutoCommit(oldCommit);
          DBUtils.releaseConn(conn, ps, rs);
      }

  }
  
}
